﻿'Description:热表排产下发类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhangrj 
'create Date:20241112
'Rewriter:
'Rewrite Date:

Imports DatabaseAccess.DBUtility
Imports BusinessRule.DTO
Imports System.Text
Imports External.APICalling
Imports BusinessRule.Common
Imports Newtonsoft.Json
Imports System.Collections.Concurrent
Imports System.Threading.Tasks
Imports System.Linq

Public Class RbApsPlanDownSendBusiness

    Private rbAps As New RbApsLimitedCapacityBusiness
    Private common As New uLMSCommonBusiness
    Private rbFactoryID = System.Configuration.ConfigurationManager.AppSettings("RBFactoryID").ToString() '热表ID
    Private toAppID_RB = System.Configuration.ConfigurationManager.AppSettings("ToAppID_RB").ToString() '推送给数据中心目标地址（工控网）

    ''' <summary>
    ''' 热表排产批次下发并派工
    ''' </summary>
    ''' <param name="dtContainer"></param>
    ''' <param name="m_UserInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RbApsDownSendAndDispatch(ByVal dtContainer As DataTable, ByVal m_UserInfo As CurrentUserInfo) As uMESProcessResult
        Dim result As uMESProcessResult = New uMESProcessResult() With {
            .Result = True
        }
        Try
            Dim sqlBl As StringBuilder = New StringBuilder()
            Dim sqlList As ArrayList = New ArrayList()
            Dim updateApsDetailSql As String = "UPDATE apsplandetail2 ad SET ad.status = '1', ad.lastchangedate = SYSDATE, ad.outemp = '{0}', ad.outdate = SYSDATE WHERE ad.containerid = '{1}' AND ad.isuse = 0 AND ad.status = '0'"
            Dim selectApsDetailSql As String = <string>SELECT ad.apsplandetailid,ad.workflowstepid,ad.qty,ad.stepinfoid,ad.starttime,ad.endtime,ad.resourceid,ad.workcenterid,ad.teamid,csd.containerstepdispatchid
                                    FROM apsplandetail2 ad 
                                    LEFT JOIN containerstepdispatch2 csd ON csd.containerid = ad.containerid AND csd.stepinfoid = ad.stepinfoid AND csd.isuse = 0
                                    WHERE ad.containerid = '{0}' AND ad.isuse = 0 AND ad.status = '0'
                                    ORDER BY ad.workflowstepid,ad.stepinfoid</string>.Value

            For Each drCon As DataRow In dtContainer.Rows
                Dim dtApsDetail As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(selectApsDetailSql, drCon("containerid").ToString()))

                If dtApsDetail.Rows.Count > 0 Then
                    Dim dtStep As DataTable = dtApsDetail.AsDataView().ToTable(True, "workflowstepid")

                    For Each drStep As DataRow In dtStep.Rows
                        Dim drsStepInfo As DataRow() = dtApsDetail.[Select](String.Format("workflowstepid = '{0}'", drStep("workflowstepid").ToString()))

                        For Each drDetail As DataRow In drsStepInfo

                            If String.IsNullOrWhiteSpace(drDetail("containerstepdispatchid").ToString()) Then
                                sqlBl.Clear()
                                sqlBl.AppendLine(<string>INSERT INTO CONTAINERSTEPDISPATCH2(CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,
                                               QTY,PLANNEDSTARTDATE,PLANNEDFINISHDATE,CONTAINERSTEPDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER,STEPINFOID) VALUES(</string>.Value)
                                sqlBl.AppendFormat("'{0}',", drCon("containerid").ToString())
                                sqlBl.AppendFormat("'{0}',", m_UserInfo.FactoryID)
                                sqlBl.AppendFormat("'{0}',", drDetail("workcenterid").ToString())
                                sqlBl.AppendFormat("'{0}',", drDetail("teamid").ToString())
                                sqlBl.AppendFormat("'{0}',", drStep("workflowstepid").ToString())
                                sqlBl.AppendFormat("'{0}',", drDetail("resourceid").ToString())
                                sqlBl.AppendFormat("{0},", drDetail("qty").ToString())
                                sqlBl.AppendFormat("to_date('{0}','yyyy-mm-dd hh24:mi:ss'),", drDetail("starttime").ToString())
                                sqlBl.AppendFormat("to_date('{0}','yyyy-mm-dd hh24:mi:ss'),", drDetail("endtime").ToString())
                                sqlBl.AppendFormat("'{0}',", Guid.NewGuid().ToString())
                                sqlBl.AppendFormat("'{0}',", m_UserInfo.EmployeeID)
                                sqlBl.AppendLine("sysdate, 0, 0,")
                                sqlBl.AppendFormat("'{0}')", drDetail("StepInfoID").ToString())
                                sqlList.Add(sqlBl.ToString())
                            Else
                                sqlBl.Clear()
                                sqlBl.AppendLine("UPDATE containerstepdispatch2 csd SET ")
                                sqlBl.AppendFormat("csd.workcenterid = '{0}',", drDetail("workcenterid").ToString())
                                sqlBl.AppendFormat("csd.teamid = '{0}',", drDetail("teamid").ToString())
                                sqlBl.AppendFormat("csd.workflowstepid = '{0}',", drStep("workflowstepid").ToString())
                                sqlBl.AppendFormat("csd.resourceid = '{0}',", drDetail("resourceid").ToString())
                                sqlBl.AppendFormat("csd.qty = {0},", drDetail("qty").ToString())
                                sqlBl.AppendFormat("csd.plannedstartdate = to_date('{0}','yyyy-mm-dd hh24:mi:ss'),", drDetail("starttime").ToString())
                                sqlBl.AppendFormat("csd.plannedfinishdate = to_date('{0}','yyyy-mm-dd hh24:mi:ss'),", drDetail("endtime").ToString())
                                sqlBl.AppendFormat("csd.employeeid = '{0}',", m_UserInfo.EmployeeID)
                                sqlBl.AppendLine("csd.createdate = SYSDATE")
                                sqlBl.AppendFormat(" WHERE csd.containerstepdispatchid = '{0}'", drDetail("containerstepdispatchid").ToString())
                                sqlList.Add(sqlBl.ToString())
                            End If
                        Next

                        If IsNotExistStepDisInfo(drCon("containerid").ToString(), drStep("workflowstepid").ToString()) Then
                            sqlBl.Clear()
                            sqlBl.AppendLine(<stirng>INSERT INTO CONTAINERSTEPDISPATCH(CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,
                                               QTY,PLANNEDSTARTDATE,PLANNEDFINISHDATE,CONTAINERSTEPDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER) VALUES(</stirng>.Value)
                            sqlBl.AppendFormat("'{0}',", drCon("containerid").ToString())
                            sqlBl.AppendFormat("'{0}',", m_UserInfo.FactoryID)
                            sqlBl.AppendFormat("'{0}',", "")
                            sqlBl.AppendFormat("'{0}',", "")
                            sqlBl.AppendFormat("'{0}',", drStep("workflowstepid").ToString())
                            sqlBl.AppendFormat("'{0}',", "")
                            sqlBl.AppendFormat("{0},", drCon("qty").ToString())
                            sqlBl.AppendFormat("null,")
                            sqlBl.AppendFormat("null,")
                            sqlBl.AppendFormat("'{0}',", Guid.NewGuid().ToString())
                            sqlBl.AppendFormat("'{0}',", m_UserInfo.EmployeeID)
                            sqlBl.AppendLine("sysdate, 0, 0)")
                            sqlList.Add(sqlBl.ToString())
                        End If
                    Next

                    sqlList.Add(String.Format(updateApsDetailSql, m_UserInfo.EmployeeID, drCon("containerid").ToString()))
                End If
                '删除推送记录，重新推送
                sqlList.Add(String.Format("DELETE sync_tablechangerecord_rb st WHERE st.tablename = UPPER('containercurrentinfo') AND st.uniquecolvalue = '{0}'", drCon("containerid").ToString()))
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Message = "下发成功"
        Catch ex As Exception
            result.Result = False
            result.Message = String.Format("下发报错：{0}", ex.Message)
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 验证转工子卡的父卡（主制分厂卡）的齐套性检查
    ''' </summary>
    ''' <param name="faContainerName"></param>
    ''' <param name="workflowstepName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsValidApsProducePrepData(ByVal faContainerName As String, ByVal workflowstepName As String) As Boolean
        'Dim param As New Dictionary(Of String, String)
        'param.Add("factoryId", rbFactoryID)
        'param.Add("ContainerName", faContainerName)
        'param.Add("WorkflowstepName", workflowstepName)
        'Dim result = rbAps.GetApsProducePrepData(param, False)
        Dim selectSQL As String = String.Format(<string>SELECT
            Case WHEN SC.SHIPCONCOMPLETESETID is null then null  
            WHEN nvl(sc.ISCOMPWORKFLOW,0)=0 or nvl(sc.ISCOMPAPS,0)=0 or nvl(sc.ISCOMPTOOL,0)=0 or nvl(sc.ISCOMPMATERIAL,0)=0 or nvl(sc.ISCOMPRESOURCE,0)=0 or nvl(sc.ISCOMPSPOTCHECK,0)=0 then 0 else 1 end ISCOMP
              FROM containercurrentinfo cci
              LEFT JOIN apsplaninfo api ON api.containerid = cci.containerid
              LEFT JOIN apsplandetail apd ON apd.apsplaninfoid = api.apsplaninfoid
              LEFT JOIN WORKFLOWSTEP ws ON ws.WORKFLOWSTEPID =apd.WORKFLOWSTEPID 
              LEFT JOIN ShipConCompleteSet sc ON sc.CONTAINERID=cci.CONTAINERID AND sc.WORKFLOWSTEPID =apd.WORKFLOWSTEPID
              WHERE cci.containername = '{0}' AND ws.workflowstepname = '{1}' AND cci.isinpool=1  AND cci.containerstatus=1 and ws.WORKFLOWSTEPNAME is not null AND apd.confirmempid IS NOT NULL 
              AND cci.originalfactoryid != '{2}' AND apd.specfactoryid = '{2}'
                                  </string>.Value, faContainerName, workflowstepName, rbFactoryID)
        Dim res = DatabaseHelper.Query_ReturnDatTable(selectSQL)
        If res.Rows.Count = 0 Then
            Return False
        End If
        If res.Select("ISCOMP = 0").Length > 0 Then
            Return False
        End If
        Return True
    End Function

    ''' <summary>
    ''' 判断是否存在工序派工
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowstepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function IsNotExistStepDisInfo(ByVal containerID As String, ByVal workflowstepID As String) As Boolean
        Dim sql As String = String.Format("SELECT 1 FROM containerstepdispatch csd WHERE csd.containerid = '{0}' AND csd.workflowstepid = '{1}' AND csd.isuse = 0", containerID, workflowstepID)

        If DatabaseHelper.GetSingle(sql) IsNot Nothing Then
            Return False
        End If

        Return True
    End Function

    ''' <summary>
    ''' 转工接收后下发派工（转工接收）
    ''' </summary>
    ''' <param name="dtContainer"></param>
    ''' <param name="m_userInfo"></param>
    ''' <remarks></remarks>
    Public Sub ShipRecordReceivedApsSend(ByVal dtContainer As DataTable, ByVal m_userInfo As CurrentUserInfo)
        Dim result As uMESProcessResult = New uMESProcessResult() With {
            .Result = True
        }
        Dim sql = <string>
                        SELECT Distinct fa.containerid facontainerid,fa.containername facontainername,c.containerid,c.containername,ws.workflowstepname,ws.workflowstepid,c.qty
                        FROM containershiprecord csr 
                        LEFT JOIN containercurrentinfo fa ON fa.containerid = csr.containerid
                        LEFT JOIN containershiprecordstep csrs ON csrs.containershiprecordid = csr.containershiprecordid
                        LEFT JOIN workflowstep ws ON ws.workflowstepid = csrs.workflowstepid
                        LEFT JOIN containersubcontainer csc ON csc.containerid = csr.containerid AND csc.workflowstepname = ws.workflowstepname
                        LEFT JOIN containercurrentinfo c ON c.containerid = csc.subcontainerid AND c.containerstatus = 1
                        WHERE csr.containershiprecordid = '{0}' AND csr.tofactoryid = '{1}'
                  </string>.Value
        Dim apsSql = "SELECT 1 FROM apsplandetail2 ap WHERE ap.containerid = '{0}' AND ap.isuse = 0"
        Dim resValid = True '验证结果
        For Each dr As DataRow In dtContainer.Rows
            resValid = True
            Dim dtSub = DatabaseHelper.Query_ReturnDatTable(String.Format(sql, dr("ContainerShipRecordID").ToString(), rbFactoryID)) '查询出该批次的转工工序相关数据
            For Each drSub As DataRow In dtSub.Rows
                '判断是否齐套，未齐套则记录日志
                If Not IsValidApsProducePrepData(drSub("facontainerid").ToString(), drSub("workflowstepname").ToString()) Then
                    common.InsertCommonMethodOperaLogInfo(drSub("facontainerid").ToString(), m_userInfo.EmployeeID, String.Format("主卡：{0}的工序：{1}未齐套", drSub("facontainername").ToString(), drSub("workflowstepname").ToString()), "转工接收并下发")
                    resValid = False
                    Exit For
                End If
                '判断是否已开子卡，未开卡则记录日志
                If String.IsNullOrWhiteSpace(drSub("containerid").ToString()) Then
                    common.InsertCommonMethodOperaLogInfo(drSub("facontainerid").ToString(), m_userInfo.EmployeeID, String.Format("主卡：{0}的工序：{1}未开子卡", drSub("facontainername").ToString(), drSub("workflowstepname").ToString()), "转工接收并下发")
                    resValid = False
                    Exit For
                End If
                '判断是否已排产，未排产则记录日志
                If DatabaseHelper.GetSingle(String.Format(apsSql, drSub("containerid").ToString())) Is Nothing Then
                    common.InsertCommonMethodOperaLogInfo(drSub("facontainerid").ToString(), m_userInfo.EmployeeID, String.Format("主卡：{0}的工序：{1}的子卡：{2}未排产", drSub("facontainername").ToString(), drSub("workflowstepname").ToString(), drSub("containername").ToString()), "转工接收并下发")
                    resValid = False
                    Exit For
                End If
            Next
            '验证没问题，则下发派工
            If resValid And dtSub.Rows.Count > 0 Then
                result = RbApsDownSendAndDispatch(dtSub.AsDataView().ToTable(True, "containerid", "qty"), m_userInfo)
                '下发失败，记录日志
                If Not result.Result Then
                    common.InsertCommonMethodOperaLogInfo(dtSub.Rows(0)("facontainerid").ToString(), m_userInfo.EmployeeID, String.Format("主卡：{0}的工序：{1}的子卡：{2}下发派工失败，原因：{3}", dtSub.Rows(0)("facontainername").ToString(), dtSub.AsEnumerable().Select(Function(p) p("workflowstepname").ToString()).Aggregate(Function(r, n) r + "," + n), dtSub.AsEnumerable().Select(Function(p) p("containername").ToString()).Aggregate(Function(r, n) r + "," + n), result.Message), "转工接收并下发")
                End If
            End If
        Next
    End Sub
End Class
